﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Employees : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    SqlCommandBuilder cb;
    DataTable dt;
    DataView dv;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {
        InitializeADOObject();
    }

    void InitializeADOObject()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        try
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getEmployees";
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Employees");
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
    }

    void loadEmployeeInfo()
    {
        dt = ds.Tables["Employees"];
        dv = new DataView(dt);

        gvEmployees.AutoGenerateSelectButton = true;
        gvEmployees.AllowPaging = true;
        gvEmployees.PageSize = 5;
        gvEmployees.DataSource = dt;
        gvEmployees.DataBind();
    }
    protected void gvEmployees_Load(object sender, EventArgs e)
    {
        if(this.IsPostBack == false)
        InitializeADOObject();
        loadEmployeeInfo();
    }

    void addNewEmployee()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "insertEmployee";
            SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar);
            param.Size = 20;
            param.Value = txtLastname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtFirstname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar);
            param.Size = 30;
            param.Value = txtTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
            param.Size = 25;
            param.Value = cbTOC.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            param.Value = txtBirthdate.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
            param.Value = txtHiredate.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtPostalCode.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar);
            param.Size = 24;
            param.Value = txtPhone.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@mgrid", SqlDbType.Int);
            param.Size = 2;
            if (txtMgrid.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtMgrid.Text.Trim();
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
        finally
        {
            con.Close();
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        //if (validateInput() == false) return;
        addNewEmployee();
        InitializeADOObject();
        loadEmployeeInfo();
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        InitializeADOObject();
        loadEmployeeInfo();
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
        btnAdd.Enabled = true;
        txtID.Text = "";
        txtLastname.Text = "";
        txtFirstname.Text = "";
        txtTitle.Text = "";
        cbTOC.SelectedIndex = -1;
        txtBirthdate.Text = "";
        txtHiredate.Text = "";
        txtAddress.Text = "";
        cbCity.SelectedIndex = -1;
        cbRegion.SelectedIndex = -1;
        txtPostalCode.Text = "";
        cbCountry.SelectedIndex = -1;
        txtPhone.Text = "";
        txtMgrid.Text = "";
    }
    protected void gvEmployees_SelectedIndexChanged(object sender, EventArgs e)
    {
        btnUpdate.Enabled = true;
        btnDelete.Enabled = true;
        btnAdd.Enabled = false;
        try
        {
            GridViewRow r = gvEmployees.SelectedRow;
            txtID.Text = r.Cells[1].Text;
            txtLastname.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtFirstname.Text = Server.HtmlDecode(r.Cells[3].Text);
            txtTitle.Text = Server.HtmlDecode(r.Cells[4].Text);
            cbTOC.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtBirthdate.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtHiredate.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[8].Text);
            cbCity.SelectedItem.Text = Server.HtmlDecode(r.Cells[9].Text);
            cbRegion.SelectedItem.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtPostalCode.Text = Server.HtmlDecode(r.Cells[11].Text);
            cbCountry.SelectedItem.Text = Server.HtmlDecode(r.Cells[12].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[13].Text);
            txtMgrid.Text = Server.HtmlDecode(r.Cells[14].Text);
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
    }

    void updateEmployee()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "updateEmployee";
            SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = txtID.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@lastname", SqlDbType.NVarChar);
            param.Size = 20;
            param.Value = txtLastname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtFirstname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar);
            param.Size = 30;
            param.Value = txtTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
            param.Size = 25;
            param.Value = cbTOC.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            param.Value = txtBirthdate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
            param.Value = txtHiredate.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtPostalCode.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = cbCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar);
            param.Size = 24;
            param.Value = txtPhone.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@mgrid", SqlDbType.Int);
            param.Size = 2;
            if (txtMgrid.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtMgrid.Text.Trim();
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
        finally
        {
            con.Close();
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        //if (!validateInput()) return;
        updateEmployee();
        InitializeADOObject();
        loadEmployeeInfo();
    }

    void deleteEmployee()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "deleteEmployee";
            SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = txtID.Text;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('Delete Completed!');</script>");
        }
        catch (Exception ex)
        {
            Response.Write(String.Format("<script>alert('{0}');</script>", ex.Message));
        }
        finally
        {
            con.Close();
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteEmployee();
        InitializeADOObject();
        loadEmployeeInfo();
    }

    void SearchByName()
    {
        dv = new DataView(dt);
        dv.RowFilter = string.Format("lastname +' '+ firstname like '%{0}%'", txtSearch.Text);
        gvEmployees.DataSource = dv;
        gvEmployees.DataBind();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SearchByName();
    }

    protected void gvEmployees_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvEmployees.PageIndex = e.NewPageIndex;
        gvEmployees.DataBind();

    }
}
